package com.erpoa.mapper;

import com.erpoa.entity.RecMatEntity;
import org.apache.ibatis.annotations.*;

import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;

/**
 * @author chenjimin
 * @Title: MaterialtransferMapper
 * @ProjectName oaerp
 * @Description: TODO
 * @date 2020/9/1015:43
 */
@Mapper
public interface MaterialtransferMapper {
    /**
     * @param wareHouse
     * @param sCode
     * @param machine
     * @return
     */
    List<RecMatEntity> selectRecMatEntityWithType(@Param("wareHouse") String wareHouse, @Param("sCode") String sCode, @Param("machine") String machine);

    /**
     * @param wareHouse
     * @param sCode
     * @param machine
     * @return
     */
    List<RecMatEntity> selectRecMatEntityWithOthers(@Param("wareHouse") String wareHouse, @Param("sCode") String sCode, @Param("machine") String machine);

    /**
     * @param fPlanID
     * @param fBarCode
     * @return
     */
    @Select(" SELECT COUNT(FID) FROM MG_RECMATDETAIL WHERE FPLANID=#{fPlanID} AND FBATCH=#{fBarCode}  ")
    int countrecMatFlag(@Param("fPlanID") String fPlanID, @Param("fBarCode") String fBarCode);

    @Select(" SELECT DISTINCT FID FID,FPARENTID,FMATCODE,nvl(FRQSQTY,0) as FRQSQTY FROM MG_RECMATDETAIL WHERE FPLANID=#{fPlanID} AND FBATCH=#{fBarCode} AND ROWNUM<2 ")
    HashMap selectrecMatWithEarliest(@Param("fPlanID") String fPlanID, @Param("fBarCode") String fBarCode);

    @Select("  SELECT A.SFA08 SFA08,A.sfa05 SFA05 FROM DS2.SFA_FILE A WHERE A.SFA01=#{fInOrderNo} AND A.SFA03=#{fMatCode} ")
    HashMap selectSfaByNoCode(@Param("fInOrderNo") String fInOrderNo, @Param("fMatCode") String fMatCode);

    @Select("SELECT NVL(SUM(CASE WHEN FOPTYPE = '领料' THEN FRSPQTY WHEN  FOPTYPE = '退料' THEN FRSPQTY*-1 END),0) AS Qty "
            + "FROM MG_RECMATDETAIL WHERE FPLANID=#{fPlanID} AND FBATCH= #{fBarCode} ")
    double selectUnQtyByIdCode(@Param("fPlanID") String fPlanID, @Param("fBarCode") String fBarCode);

    @Select("  SELECT DISTINCT FPRODUCTYPE FPRODUCTYPE FROM MG_RECMAT WHERE FPLANID= #{fPlanID} AND FMATCODE=  #{fBarCode} AND FPRODUCTYPE IS NOT NULL ")
    List<String> selectRecMatByplanIdCode(@Param("fPlanID") String fPlanID, @Param("fBarCode") String fBarCode);


    @Select(" SELECT NVL(MIN(NVL(A.FHAVESENDQTY,0) - (CASE WHEN B.FNEEDSENDQTY = 0 THEN 1 ELSE B.FNEEDSENDQTY END)),0) "
            + " AS LDIFFRESULT  FROM ( SELECT FMATCODE,NVL(SUM(CASE WHEN FOPTYPE = '领料' THEN FRSPQTY WHEN  FOPTYPE = '退料'"
            + " THEN FRSPQTY*-1 END),0)  AS FHAVESENDQTY FROM MG_RECMAT WHERE FPLANID = #{fPlanID}  " +
            "AND FPRODUCTYPE=#{fType} GROUP BY FMATCODE ) A  RIGHT JOIN"
            + " (SELECT SFA01,SFA03,ROUND(SFA161*(SELECT FPLANQTY FROM MG_ORDERPLAN WHERE FID=#{fPlanID}), 3) FNEEDSENDQTY FROM "
            + "DS2.SFA_FILE WHERE SFA03=(SELECT DISTINCT FMATCODE FROM MG_RECMAT WHERE FPLANID=#{fPlanID} AND FPRODUCTYPE=#{fType} ) "
            + " AND SFA01 = (SELECT FORDERNO FROM MG_ORDERPLAN WHERE FID=#{fPlanID}) ) B ON A.FMATCODE = B.SFA03")
    double selectYLQtyByplanIdType(@Param("fPlanID") String fPlanID, @Param("fType") String fType);

    @Select(" select distinct FPROCSS FPROCSS ,FUNIT FUNIT from dsx5.mg_recmat where forderno = #{fOrderNo} and fmatcode = #{fMatCode} ")
    List<HashMap> selectProcessByNoCode(@Param("fOrderNo") String fOrderNo, @Param("fMatCode") String fMatCode);

    @Select(" select sfa05,sfa161 from ds2.sfa_file where sfa01 = #{fOrderNo} and sfa03 = #{fMatCode} and sfa08= #{fProcss} and sfa12 = #{fUnit} ")
    List<HashMap> selectSfaQPAByNoCode(@Param("fOrderNo") String fOrderNo, @Param("fMatCode") String fMatCode, @Param("fProcss") String fProcss, @Param("fUnit") String fUnit);

    @Select(" select nvl(sum(CEIL(ROUND(FPLANQTY * ${QPA},3))),0) as sumQty from dsx5.mg_orderplan where forderno = #{forderno} and fclassjob  in ( 'F','R') and fsendorder = 1 and fisfinishproduced = 'Y'")
    BigDecimal selectsumQtyByordernopen(@Param("forderno") String forderno, @Param("QPA") BigDecimal QPA);

    @Select("select nvl(sum(ROUND(FPLANQTY *  ${QPA} , 3)),0) as sumQty from dsx5.mg_orderplan where forderno = #{forderno} and fclassjob  in ( 'F','R') and fsendorder = 1 and fisfinishproduced = 'Y'")
    BigDecimal selectsumQtyByordernoink(@Param("forderno") String forderno, @Param("QPA") BigDecimal QPA);

    @Select(" select NVL(SUM(CASE WHEN FOPTYPE = '领料' THEN FRSPQTY  WHEN FOPTYPE = '退料' THEN FRSPQTY * -1 END), 0) AS OVEROUTMAT" +
            " from dsx5.mg_recmatdetail where forderno = #{forderno} and fmatcode = #{fMatCode} ")
    BigDecimal selectsumMESQtyByorderno(@Param("forderno") String forderno, @Param("fMatCode") String fMatCode);

    @Select(" SELECT  NVL(SUM(NVL(FRQSQTY, 0)) - SUM(NVL(FRSPQTY, 0)),0) FROM MG_RECMAT WHERE FPLANID=#{fInPlanID} " +
            " AND FMATCODE=#{fMatCode} AND NVL(FRQSQTY,0)<>NVL(FRSPQTY,0) AND FOPTYPE='领料'")
    double selectCountActual(@Param("fInPlanID") String fInPlanID, @Param("fMatCode") String fMatCode);

    @Select(" SELECT nvl(A.TC_IMAM08,'无') TC_IMAM08 ,nvl(A.TC_IMAM09,'无') TC_IMAM09 FROM DS2.TC_IMAM_FILE A,MG_RECMAT B WHERE A.TC_IMAM01=B.FMATCODE AND "
            + "A.TC_IMAM16=B.FFACTORYCODE AND A.TC_IMAM17=B.FWORKSHOPCODE AND B.FID=#{fRecID}")
    HashMap selectWareLocation(@Param("fRecID") String fRecID);

    @Select("SELECT SFB04 FROM DS2.SFB_FILE WHERE SFB01=#{fOrderNo} ")
    String getOrderStatus(@Param("fOrderNo") String fOrderNo);

    @Select("SELECT COUNT(SFA01) FROM DS2.SFA_FILE WHERE SFA01=#{fOrderNo} AND SFA03=#{fMatCode}  AND SFA062>0")
    int countsfaSupercollar(@Param("fOrderNo") String fOrderNo, @Param("fMatCode") String fMatCode);

    @Insert("INSERT INTO MG_RECMAT(FID,VERSION ,FPLANID,FMACHINECODE,FORDERNO, FMATCODE,FMATNAME,FOPTYPE,FRQSPER,"
            + "FRQSTIME,FRQSQTY,FRSPQTY,FRSPPER,FRSPTIME ,FWARECODE,FSETS ,FREASON,FSEATCODE,FUNIT,FFACTORYCODE,FFACTORYNAME,FWORKSHOPCODE,"
            + "FWORKSHOPNAME, FPROCSS,FDEALSTATUS,FVENDERNAME,FPRODUCTYPE )SELECT #{outRecMatID},0 ,FPLANID,FMACHINECODE,FORDERNO, FMATCODE,FMATNAME,"
            + "'退料',#{sCode},sysdate,#{originalQty},#{originalQty},#{sCode},sysdate ,#{fWareCode},FSETS ,'转料',#{fSeatCode},FUNIT,FFACTORYCODE,FFACTORYNAME,FWORKSHOPCODE ,FWORKSHOPNAME,"
            + " FPROCSS,FDEALSTATUS,FVENDERNAME,FPRODUCTYPE FROM MG_RECMAT WHERE FID=#{fRecID} ")
    int insertOutRecmatbyfid(@Param("outRecMatID") String outRecMatID, @Param("sCode") String sCode, @Param("originalQty") double originalQty,
                             @Param("fWareCode") String fWareCode, @Param("fSeatCode") String fSeatCode, @Param("fRecID") String fRecID);

    @Insert("INSERT INTO MG_RECMATDETAIL (FID ,VERSION,FPLANID ,FMACHINECODE,FORDERNO,FMATCODE,FMATNAME,FOPTYPE,FRQSPER,"
            + "FRQSTIME,FRQSQTY ,FRSPQTY ,FRSPPER,FRSPTIME,FWARECODE,FSETS,FREASON,FSEATCODE,FUNIT,FFACTORYCODE,FFACTORYNAME,FWORKSHOPCODE,"
            + "FWORKSHOPNAME,FPROCSS,FPARENTID,FISHANDOVER,FCODE ,FSEQCODE,FBATCH) SELECT #{mg_recmatdetailfid2} ,0,FPLANID ,FMACHINECODE,FORDERNO,FMATCODE,FMATNAME,"
            + "'退料',#{sCode},sysdate,#{originalQty} ,#{originalQty} ,#{sCode},sysdate,#{fWareCode},FSETS,'转料',#{fSeatCode},FUNIT,FFACTORYCODE,FFACTORYNAME,FWORKSHOPCODE,FWORKSHOPNAME,"
            + "FPROCSS,#{OutRecMatID},'Y',FCODE ,FSEQCODE,#{fBarCode} FROM MG_RECMATDETAIL  WHERE FID=#{detailID}")
    int insertOutDetail(@Param("mg_recmatdetailfid2") String mg_recmatdetailfid2, @Param("sCode") String sCode, @Param("originalQty") double originalQty,
                        @Param("fWareCode") String fWareCode, @Param("fSeatCode") String fSeatCode, @Param("OutRecMatID") String OutRecMatID,
                        @Param("fBarCode") String fBarCode, @Param("detailID") String detailID);

    @Insert("INSERT INTO TC_X5_FILE(TC_X501,TC_X502,TC_X503,TC_X504,TC_X505,TC_X506,TC_X507,TC_X509,TC_X510,TC_X511,"
            + "TC_X513,TC_X514,TC_X515,TC_X516,TC_X5ID) SELECT FFACTORYCODE,FWORKSHOPCODE,FORDERNO,FMATCODE,2,#{fWareCode},#{fSeatCode},#{originaloradjustQty},sysdate,#{sCode},"
            + "#{fBarCode},FMACHINECODE,'转料',FPROCSS ,#{mg_recmatdetailfid2} FROM MG_RECMAT WHERE FID=#{fRecID}")
    int insertOutX5byfRecID(@Param("fWareCode") String fWareCode, @Param("fSeatCode") String fSeatCode,
                            @Param("originaloradjustQty") double originaloradjustQty, @Param("sCode") String sCode,
                            @Param("fBarCode") String fBarCode, @Param("mg_recmatdetailfid2") String mg_recmatdetailfid2, @Param("fRecID") String fRecID);

    @Insert("insert into mg_batchmatrecord" +
            "  (fid, parantid, fbarcode, fmatcode, fmatname, frecordtime, fqty, ffactorycode, fworkshopcode,FUNCTIONNAME,USERNAME,TYPE," +
            "FMACHINECODE,FORDERNO,FWARECODE,FWARENAME,FSEATCODE,FSEATNAME,FPROCSS)" +
            " SELECT #{bCUUID},#{outRecMatID},#{fBarCode},FMATCODE,FMATNAME,sysdate,#{originalQty}," +
            "FFACTORYCODE,FWORKSHOPCODE,#{xhllztlbc},#{sCode},#{fType},FMACHINECODE,FORDERNO,FWARECODE,FWARENAME,FSEATCODE,FSEATNAME,FPROCSS" +
            " FROM MG_RECMAT WHERE FID=#{fRecID}")
    int insertmgbatchmatrecord(@Param("bCUUID") String bCUUID, @Param("outRecMatID") String outRecMatID, @Param("fBarCode") String fBarCode,
                               @Param("originalQty") double originalQty, @Param("xhllztlbc") String xhllztlbc, @Param("sCode") String sCode,
                               @Param("fType") String fType, @Param("fRecID") String fRecID);

    @Insert("INSERT INTO MG_RECMAT(FID,VERSION ,FPLANID,FMACHINECODE,FORDERNO, FMATCODE,FMATNAME,FOPTYPE, FRQSPER,FRQSTIME,"
            + "FRQSQTY,FRSPQTY,FRSPPER,FRSPTIME,FWARECODE,FSETS,FREASON,FSEATCODE,FUNIT,FFACTORYCODE,FFACTORYNAME,FWORKSHOPCODE ,FWORKSHOPNAME,"
            + " FPROCSS,FDEALSTATUS,FVENDERNAME,FPRODUCTYPE )SELECT #{recMatID},0,#{fInPlanID},#{fInMachineCode},#{fInOrderNo}, FMATCODE,FMATNAME," +
            "'领料',#{sCode},sysdate,#{originaloradjustQty},#{originaloradjustQty},#{sCode},sysdate,"
            + "#{fWareCode},FSETS ,'转料',#{fSeatCode},FUNIT,FFACTORYCODE,FFACTORYNAME,FWORKSHOPCODE ,FWORKSHOPNAME, " +
            "#{inProcss},FDEALSTATUS,FVENDERNAME,FPRODUCTYPE"
            + " FROM MG_RECMAT WHERE FID=#{fRecID} ")
    int insertInRecmatByfid(@Param("recMatID") String recMatID, @Param("fInPlanID") String fInPlanID, @Param("fInMachineCode") String fInMachineCode,
                            @Param("fInOrderNo") String fInOrderNo, @Param("sCode") String sCode, @Param("originaloradjustQty") double originaloradjustQty,
                            @Param("fWareCode") String fWareCode, @Param("fSeatCode") String fSeatCode, @Param("inProcss") String inProcss, @Param("fRecID") String fRecID);

    @Insert("INSERT INTO MG_RECMATDETAIL (FID ,VERSION,FPLANID ,FMACHINECODE,FORDERNO,FMATCODE,FMATNAME,FOPTYPE,FRQSPER,"
            + "FRQSTIME,FRQSQTY ,FRSPQTY ,FRSPPER,FRSPTIME,FWARECODE,FSETS,FREASON,FSEATCODE,FUNIT,FFACTORYCODE,FFACTORYNAME,FWORKSHOPCODE,"
            + "FWORKSHOPNAME,FPROCSS,FPARENTID,FISHANDOVER,FCODE ,FSEQCODE,FBATCH) SELECT #{mg_recmatdetaiLfid5} ,0,#{fInPlanID} ,#{fInMachineCode}," +
            "#{fInOrderNo},FMATCODE,FMATNAME,'领料'," +
            "#{sCode},sysdate,#{originaloradjustQty},"
            + "#{originaloradjustQty} ,#{sCode},sysdate,#{fWareCode},FSETS,'转料',#{fSeatCode},FUNIT,FFACTORYCODE,FFACTORYNAME,FWORKSHOPCODE," +
            "FWORKSHOPNAME,#{inProcss},#{recMatID},'Y',FCODE,"
            + "FSEQCODE,#{fBarCode} FROM MG_RECMATDETAIL  WHERE FID=#{detailID}")
    int insertInRecmatDetailbydetailid(@Param("mg_recmatdetaiLfid5") String mg_recmatdetaiLfid5, @Param("fInPlanID") String fInPlanID,
                                       @Param("fInMachineCode") String fInMachineCode, @Param("fInOrderNo") String fInOrderNo,
                                       @Param("sCode") String sCode, @Param("originaloradjustQty") double originaloradjustQty,
                                       @Param("fWareCode") String fWareCode, @Param("fSeatCode") String fSeatCode, @Param("inProcss") String inProcss,
                                       @Param("recMatID") String recMatID, @Param("fBarCode") String fBarCode, @Param("detailID") String detailID);

    @Insert("INSERT INTO TC_X5_FILE(TC_X501,TC_X502,TC_X503,TC_X504,TC_X505,TC_X506,TC_X507,TC_X509,TC_X510,TC_X511,"
            + "TC_X513,TC_X514,TC_X515,TC_X516,TC_X5ID) SELECT FFACTORYCODE,FWORKSHOPCODE,#{fInOrderNo},FMATCODE,1," +
            "#{fWareCode},#{fSeatCode},#{originaloradjustQty},sysdate,#{sCode},"
            + "#{fBarCode},#{fInMachineCode},'转料',#{inProcss} ,#{mg_recmatdetaiLfid5} FROM MG_RECMAT WHERE FID=#{fRecID}")
    int insertInX5SQLbyrecid(@Param("fInOrderNo") String fInOrderNo, @Param("fWareCode") String fWareCode, @Param("fSeatCode") String fSeatCode,
                             @Param("originaloradjustQty") double originaloradjustQty, @Param("sCode") String sCode, @Param("fBarCode") String fBarCode,
                             @Param("fInMachineCode") String fInMachineCode, @Param("inProcss") String inProcss,
                             @Param("mg_recmatdetaiLfid5") String mg_recmatdetaiLfid5, @Param("fRecID") String fRecID);

    @Insert("insert into mg_batchmatrecord" +
            "  (fid, parantid, fbarcode, fmatcode, fmatname, frecordtime, fqty, ffactorycode, fworkshopcode,FUNCTIONNAME,USERNAME,TYPE,FMACHINECODE,FORDERNO,FWARECODE,FSEATCODE,FPROCSS)" +
            " SELECT #{bRUUID},#{recMatID},#{fBarCode},FMATCODE,FMATNAME,sysdate,#{originaloradjustQty},FFACTORYCODE,FWORKSHOPCODE,#{xhllzllbr}," +
            "#{sCode},#{fType},#{fInMachineCode},#{fInOrderNo},#{fWareCode},#{fSeatCode},#{inProcss}" +
            " FROM MG_RECMAT WHERE FID=#{fRecID}")
    int insertBatchMatByrecid(@Param("bRUUID") String bRUUID, @Param("recMatID") String recMatID, @Param("fBarCode") String fBarCode,
                              @Param("originaloradjustQty") double originaloradjustQty, @Param("xhllzllbr") String xhllzllbr,
                              @Param("sCode") String sCode, @Param("fType") String fType, @Param("fInMachineCode") String fInMachineCode,
                              @Param("fInOrderNo") String fInOrderNo, @Param("fWareCode") String fWareCode, @Param("fSeatCode") String fSeatCode,
                              @Param("inProcss") String inProcss, @Param("fRecID") String fRecID);

    @Update("UPDATE MG_RECMAT SET FRQSQTY=nvl(FRSPQTY,0) WHERE FPLANID IN (#{fInPlanID},#{fPlanID}) AND FMATCODE=#{fMatCode} AND NVL(FRQSQTY,0)<>NVL(FRSPQTY,0)")
    int updateMatByparam(@Param("fInPlanID") String fInPlanID, @Param("fPlanID") String fPlanID, @Param("fMatCode") String fMatCode);

    @Insert("INSERT INTO MG_RECMAT(FID,VERSION ,FPLANID,FMACHINECODE,FORDERNO, FMATCODE,FMATNAME,FOPTYPE, FRQSPER,FRQSTIME,"
            + "FRQSQTY,FWARECODE,FSETS,FREASON,FSEATCODE,FUNIT,FFACTORYCODE,FFACTORYNAME,FWORKSHOPCODE ,FWORKSHOPNAME,"
            + " FPROCSS,FDEALSTATUS,FVENDERNAME,FPRODUCTYPE )SELECT #{uuid},0,#{fInPlanID},#{fInMachineCode},#{fInOrderNo}, FMATCODE,FMATNAME,'领料'," +
            "#{sCode},sysdate,#{applyqty}, "
            + " #{fWareCode},FSETS ,'转料余量',#{fSeatCode},FUNIT,FFACTORYCODE,FFACTORYNAME,FWORKSHOPCODE ,FWORKSHOPNAME," +
            " #{inProcss},FDEALSTATUS,FVENDERNAME,FPRODUCTYPE"
            + " FROM MG_RECMAT WHERE FID=#{fRecID} ")
    int insertinsertRecmatbyfrecid(@Param("uuid") String uuid, @Param("fInPlanID") String fInPlanID, @Param("fInMachineCode") String fInMachineCode,
                                   @Param("fInOrderNo") String fInOrderNo, @Param("sCode") String sCode, @Param("applyqty") double applyqty,
                                   @Param("fWareCode") String fWareCode, @Param("fSeatCode") String fSeatCode, @Param("inProcss") String inProcss,
                                   @Param("fRecID") String fRecID);

    @Update("UPDATE MG_RECMATDETAIL A SET A.FRQSQTY =(SELECT NVL(B.FRQSQTY,0) FROM MG_RECMAT B WHERE A.FPARENTID=B.FID ) WHERE A.FPLANID " +
            "IN (#{fInPlanID},#{fPlanID})  AND A.FMATCODE=#{fMatCode} ")
    int updateOutRecmatdetailbyparam(@Param("fInPlanID") String fInPlanID, @Param("fPlanID") String fPlanID, @Param("fMatCode") String fMatCode);


    @Select(" SELECT NVL(MIN(NVL(A.FHAVESENDQTY,0) - (CASE WHEN B.FNEEDSENDQTY = 0 THEN 1 ELSE B.FNEEDSENDQTY END)),0) "
            + " AS LDIFFRESULT  FROM ( SELECT FMATCODE,NVL(SUM(CASE WHEN FOPTYPE = '领料' THEN FRSPQTY WHEN  FOPTYPE = '退料'"
            + " THEN FRSPQTY*-1 END),0)  AS FHAVESENDQTY FROM MG_RECMAT WHERE FPLANID = #{fPlanID}  AND FPRODUCTYPE=#{fType} GROUP BY FMATCODE ) " +
            "A  RIGHT JOIN"
            + " (SELECT SFA01,SFA03,ROUND(SFA161*(SELECT FPLANQTY FROM MG_ORDERPLAN WHERE FID=#{fPlanID}), 3) FNEEDSENDQTY FROM "
            + "DS2.SFA_FILE WHERE SFA03=(SELECT DISTINCT FMATCODE FROM MG_RECMAT WHERE FPLANID=#{fPlanID} AND FPRODUCTYPE=#{fType} ) "
            + " AND SFA01 = (SELECT FORDERNO FROM MG_ORDERPLAN WHERE FID=#{fPlanID}) ) B ON A.FMATCODE = B.SFA03")
    Double selectallowanceLDIFFRESULTByParm(@Param("fPlanID") String fPlanID, @Param("fType") String fType);


    @Select("SELECT FBATCH FROM (SELECT * FROM MG_RECMATDETAIL WHERE FPARENTID IN" +
            " (SELECT FID FROM MG_RECMAT WHERE FPLANID=#{fPlanID} AND FPRODUCTYPE=#{fType} AND FOPTYPE='领料') ORDER BY FRSPTIME DESC ) WHERE ROWNUM<2 ")
    String selectallastfbatchByParm(@Param("fPlanID") String fPlanID, @Param("fType") String fType);
}
